﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsCases

    Dim FolderPath As String = FTPAddress + "Prospects Cases"

    Sub FillProspectCases()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Cond, CondClassFilfter As String

            'Check Class of Business filter
            If Me.RClassGeneral.Checked = True Then
                CondClassFilfter = "General"

            ElseIf Me.RClassMedical.Checked = True Then
                CondClassFilfter = "Medical "

            ElseIf Me.RClassLife.Checked = True Then
                CondClassFilfter = "Life"

            ElseIf Me.RClassMotor.Checked = True Then
                CondClassFilfter = "Motor"
            End If

            'Check Status filter
            If Me.RShowAll.Checked = True Then
                Cond += ""

            ElseIf Me.RActive.Checked = True Then
                Cond += "and Status=N'Active' "

            ElseIf Me.RWon.Checked = True Then
                Cond += "and Status=N'Won' "

            ElseIf Me.RLost.Checked = True Then
                Cond += "and Status=N'Lost' "

            ElseIf Me.RNonDesired.Checked = True Then
                Cond += "and Status=N'Non Desired' "
            End If

            Dim cmd As New SqlCommand("Select * From ProspectCases Where AssignedTo =N'" & CurrentUser & "' and Branch=N'" & Branch & "' and " & _
                                      "ClassofBusiness Like N'%" & CondClassFilfter & "%' " & Cond, cnn)
            Dim Reader As SqlDataReader
            Dim ExpiryDate As String

            Me.GridProspects.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("ExpiryDate") Is DBNull.Value Then
                    ExpiryDate = ""
                Else
                    ExpiryDate = CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridProspects.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("ProsNo"), Reader.Item("ProspectID"), Reader.Item("ProspectName"), _
                                                        Reader.Item("Status"), ExpiryDate, Reader.Item("InsuranceCompany"), Reader.Item("InsuranceBroker"), _
                                                        Reader.Item("ClassofBusiness"), Reader.Item("BusinessSize"), Reader.Item("AssignedBy"), _
                                                         CDate(Reader.Item("TransDate")).ToString("yyyy/MM/dd"), Reader.Item("AssignedTo"), Reader.Item("SavedUser"), _
                                                        "Edit", "Delete"})
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridProspects.Rows
                If row.Cells(4).Value = "Active" Then
                    row.Cells(4).Style.BackColor = Color.Yellow

                ElseIf row.Cells(4).Value = "Won" Then
                    row.Cells(4).Style.BackColor = Color.GreenYellow

                ElseIf row.Cells(4).Value = "Lost" Then
                    row.Cells(4).Style.BackColor = Color.Red

                ElseIf row.Cells(4).Value = "Non Desired" Then
                    row.Cells(4).Style.BackColor = Color.Silver
                End If
            Next

            'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridProspects.Rows
                If Row.Cells(5).Value <> "" Then
                    If Now.Date >= CType(Row.Cells(5).Value, Date).Date Then
                        Row.Cells(5).Style.BackColor = Color.Red
                    Else
                        Row.Cells(5).Style.BackColor = Color.White
                    End If
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridProspects_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProspects.CellClick
        If e.ColumnIndex = 14 Then
            Dim a As New frmProspectsCasesEdit
            a.SNo = CInt(Me.GridProspects.CurrentRow.Cells(0).Value)
            a.ShowDialog()
            FillProspectCases()

        ElseIf e.ColumnIndex = 15 Then
            Try
                If MsgBox("Confirm deleting: " & Me.GridProspects.CurrentRow.Cells(1).Value, MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From ProspectCases Where SNo=" & Me.GridProspects.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    FillProspectCases()

                    Me.Cursor = Cursors.Default
                End If
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridClaims_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProspects.CellDoubleClick
        Dim a As New frmProspectsCasesStatus
        a.SNo = CInt(Me.GridProspects.CurrentRow.Cells(0).Value)
        a.ShowDialog()
        FillProspectCases()
    End Sub

    Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Sub FillProspectsNotes()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ProspectsNotes Where ProsNo=" & Me.GridProspects.CurrentRow.Cells(0).Value & "Order By SNo", cnn1)
            Dim Reader As SqlDataReader

            Me.txtProgressNotes.Clear()

            Dim ListHeader, ListNotes As New ArrayList

            cnn1.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.txtProgressNotes.Text += Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd HH:mm") & _
                                        Chr(13) & Reader.Item("Notes") & ".    " & Reader.Item("Type") & Chr(13) & Chr(13)

                ListHeader.Add(Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd HH:mm"))
                ListNotes.Add(Reader.Item("Notes"))
            End While
            cnn1.Close()

            For Each T As String In ListHeader
                txtProgressNotes.SelectionStart = txtProgressNotes.Find(T)
                Dim bfont As New Font(txtProgressNotes.Font, FontStyle.Bold)
                txtProgressNotes.SelectionFont = bfont
                txtProgressNotes.SelectionColor = Color.Blue
            Next

            Me.txtProgressNotes.ScrollToCaret()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProspectsDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.GridProspects.CurrentRow.Cells(1).Value) 'sub is prospect no!
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            'list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.Name, Fi.CreationTime.ToString("yyyy/MM/dd hh:mm tt"), "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub SaveNotes()
        If Me.txtNotes.Text.Trim.Length = 0 Then
            MsgBox("Please enter your message")
            Me.txtNotes.Clear()
            Me.txtNotes.Focus()
            Exit Sub
        Else
            Try
                Dim Type As String
                If RadPhoneCall.Checked = True Then
                    Type = "Phone Call"
                ElseIf RadVisit.Checked = True Then
                    Type = "Visit"
                ElseIf RadOther.Checked = True Then
                    Type = "Other"
                End If

                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Insert Into ProspectsNotes (ProsNo,Notes,Type,SavedUser) Values " & _
                                          "(@ProsNo,@Notes,@Type,@SavedUser)", cnn)
                cnn.Open()

                'Add values
                cmd.Parameters.AddWithValue("@ProsNo", Me.GridProspects.CurrentRow.Cells(0).Value)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text.Trim)
                cmd.Parameters.AddWithValue("@Type", Type)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()
                cnn.Close()

                FillProspectsNotes()

                Me.txtNotes.Clear()
                Me.txtNotes.Focus()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub frmProspectsRegistry_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

    End Sub

    Sub Clear()
        Me.txtProspectCaseNo.Clear()
        Me.txtNotes.Clear()
        Me.txtProgressNotes.Clear()
        Me.GridFiles.Rows.Clear()
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub GridProspects_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridProspects.CurrentCellChanged
        Try
            Me.Cursor = Cursors.WaitCursor

            If Me.GridProspects.CurrentRow Is Nothing Then
                Me.Cursor = Cursors.Default
            Else
                Me.txtProspectCaseNo.Text = Me.GridProspects.CurrentRow.Cells(1).Value
                FillProspectsNotes()
                FillProspectsDocuments()
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SaveNotes()
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = 2 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim FilePath As String = FolderPath + "\" + Me.GridProspects.CurrentRow.Cells(1).Value + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value
                System.Diagnostics.Process.Start(FilePath)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub RShowAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RShowAll.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RActive_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RActive.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RWon_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RWon.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RLost_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RLost.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RNonDesired_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RNonDesired.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub ToolStripButton5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton5.Click
        Try
            Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Dim File As New FileInfo(Me.OpenFileDialog1.FileName)
                File.CopyTo(FolderPath & "\" & Me.GridProspects.CurrentRow.Cells(1).Value & "\" & File.Name)

                FillProspectsDocuments()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RClassAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RClassAll.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RClassGeneral_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RClassGeneral.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RClassMedical_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RClassMedical.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RClassLife_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RClassLife.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub RClassMotor_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RClassMotor.CheckedChanged
        FillProspectCases()
    End Sub

    Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
        Dim a As New frmProspectsFileNew
        a.Show()
    End Sub

    Private Sub ToolStripButton6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton6.Click
        Dim a As New frmProspectsCasesNew
        a.ShowDialog()

        FillProspectCases()
    End Sub
End Class
